How to Query a JSON Column

SQL Tutorial Home

MS-SQL Server can store json documents in nvarchar columns. If you want to access a sub-element of the json document, you can use the function json_value. There is a function called isjson to verify if a given column is a valid json document. In the code below we can see some examples:

Let’s create a table with a nvarchar column and populate it with a valid json document:

create table emp_json_tbl ( json_col nvarchar(250) );

insert into emp_json_tbl values ( 
    '{"emp_id":2,"last_name":"Lopez","dep_id":100,"salary":900,"department":null}'
                                );
insert into emp_json_tbl values (
'{"emp_id":1,"last_name":"Graue","dep_id":100,"salary":1000,"department":null}'
                                );

In the next query we will extract the element last_name from the column json_col, in the where clause we validate if the value of json_col column is a valid json document using isjson.

select json_value(json_col, '$.last_name') AS last_name_json 
from emp_json_tbl
where isjson(json_col) = 1
last_name_json
Graue
Lopez 
select json_value(json_col, '$.emp_id') AS emp_id, 
       json_value(json_col, '$.last_name') AS last_name
from emp_json_tbl
where isjson(json_col) = 1
  and json_value(json_col, '$.salary') < 1000
emp_id last_name_json
2 Lopez

IN THIS PAGE